<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class kuesioner_model extends arfaModel {
    function __construct() {
        parent::__construct();
		$this->table_name	= "kuesioner";
		$this->primary_key	= "kd_kuesioner";
		$this->order_by		= "kd_kuesioner ASC";
    }
	function get_chart($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		kuesioner.kd_kuesioner,
		kuesioner_jawaban.nilai,
		kuesioner.jenis_kelamin,
		kuesioner.TGL_KUESIONER,
		SUM(nilai) / COUNT(kuesioner.kd_kuesioner) AS HASIL
		FROM kuesioner
		JOIN kuesioner_jawaban ON kuesioner_jawaban.kd_kuesioner = kuesioner.kd_kuesioner
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY kuesioner.kd_kuesioner");
		return $query->result_array();
	}
	function get_chart_bar($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		master_pertanyaan.URUTAN,
		SUM(nilai) / COUNT(URUTAN) AS HASIL
		FROM master_pertanyaan
		JOIN kuesioner_jawaban ON kuesioner_jawaban.ID_PERTANYAAN = master_pertanyaan.ID_PERTANYAAN
		JOIN kuesioner ON kuesioner.kd_kuesioner = kuesioner_jawaban.kd_kuesioner
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY master_pertanyaan.URUTAN
		");
		return $query->result_array();
	}
	function get_chart_usia($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		master_usia.range_usia,
		kuesioner.kd_usia,
		COUNT(kuesioner.kd_usia) as USIA
		FROM kuesioner
		JOIN master_usia ON master_usia.kd_usia = kuesioner.kd_usia
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY kuesioner.kd_usia");
		return $query->result_array();
	}
	function get_chart_perkerjaan($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		master_pekerjaan.nm_pekerjaan,
		kuesioner.kd_pekerjaan,
		COUNT(kuesioner.kd_pekerjaan) as PEKERJAAN
		FROM kuesioner
		JOIN master_pekerjaan ON master_pekerjaan.kd_pekerjaan = kuesioner.kd_pekerjaan		
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY kuesioner.kd_pekerjaan");
		return $query->result_array();
	}
	function get_chart_pendidikan($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		master_pendidikan.nm_pendidikan,
		kuesioner.kd_pendidikan,
		COUNT(kuesioner.kd_pendidikan) as PENDIDIKAN
		FROM kuesioner
		JOIN master_pendidikan ON master_pendidikan.kd_pendidikan = kuesioner.kd_pendidikan		
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY kuesioner.kd_pendidikan");
		return $query->result_array();
	}
	function get_chart_izin($tgl_awal,$tgl_akhir){
		$query	= $this->db->query("
		SELECT
		master_urus_izin.urus_izin,
		kuesioner.kd_urus_izin,
		COUNT(kuesioner.kd_urus_izin) as IZIN
		FROM kuesioner
		JOIN master_urus_izin ON master_urus_izin.kd_urus_izin = kuesioner.kd_urus_izin		
		WHERE TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
		GROUP BY kuesioner.kd_urus_izin");
		return $query->result_array();
	}
}